Databases with R (part 2)

.title[
# Databases with R (part 2)
]
.subtitle[
## <a href="https://pos.it/databases-24" class="uri">https://pos.it/databases-24</a>
]
.author[
### Kirill Müller
]
.author[
### cynkra GmbH
]
.date[
### August 12, 2024
]

---

.fansi.fansi-error {
    color: #D70040;
}

pre {
 white-space: pre-wrap;       /* css-3 */
 white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
 white-space: -pre-wrap;      /* Opera 4-6 */
 white-space: -o-pre-wrap;    /* Opera 7 */
 word-wrap: break-word;       /* Internet Explorer 5.5+ */
 margin-bottom: 0px;
}

.reg-margin-bottom {
  margin-bottom: 25px;
}

.remark-slide-content {
  padding-top: 0px;
  padding-bottom: 0px;
}
.remark-slide-scaler {
  overflow-y: auto;
}

.font17 {
    font-size: 17px;
}
.font14 {
    font-size: 14px;
}

.script-number-font {
  color: inherit !important;
  opacity: 0.9 !important;
  transform: scale(1.8);
  transform-origin: top left;
}

.script-number {
  color: inherit !important;
  opacity: 0.9 !important;
  position: absolute;
  top: 12px;
  right: 20px;
  transform: scale(1.8);
  transform-origin: top right;
}

.exercise-timer {
  position: fixed;
  bottom: 12px;
  left: 20px;
  transform: scale(1.8);
  transform-origin: bottom left;
}

.exercise-timer img {
  max-width: none; /* Prevent the image from being constrained by the container */
}

</style>

# Learning goals

---

---

background-image: url("data:image/png;base64,#images/21.webp")
background-size: contain
background-position: 100% 100%

# Working with database dumps

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_21.R"><title>Script: db_21.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_21.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_21.R</text></g></svg></div>

```r
library(DBI)
library(dplyr)
requireNamespace("duckdb")
requireNamespace("duckplyr")
```
]

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: contain
background-position: 100% 100%

# Working with database dumps

```r
library(DBI)
library(dplyr)
requireNamespace("duckdb")
requireNamespace("duckplyr")
```
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Prepare dump

Imagine someone gave you a Parquet file.

``` r
arrow::write_parquet(nycflights13::flights, "flights.parquet")

fs::file_size("flights.parquet")
```

```
5.43M
```

``` r
lobstr::obj_size(nycflights13::flights)
```

```
40.65 MB
```

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_21.R"><title>Script: db_21.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_21.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_21.R</text></g></svg></div>

---

# Processing the local data

``` r
df <- arrow::read_parquet("flights.parquet")
df
```

<PRE class="fansi fansi-output"><CODE># A tibble: 336,776 × 19
 year month day dep_t…¹ sched…² dep_d…³ arr_t…⁴ sched…⁵ arr_d…⁶ carrier flight tailnum
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;chr&gt; &lt;int&gt; &lt;chr&gt; 
1 2013 1 1 517 515 2 830 819 11 UA 1545 N14228 
2 2013 1 1 533 529 4 850 830 20 UA 1714 N24211 
3 2013 1 1 542 540 2 923 850 33 AA 1141 N619AA 
4 2013 1 1 544 545 -1 1004 1022 -18 B6 725 N804JB 
5 2013 1 1 554 600 -6 812 837 -25 DL 461 N668DN 
6 2013 1 1 554 558 -4 740 728 12 UA 1696 N39463 
# ℹ 336,770 more rows
# ℹ abbreviated names: ¹dep_time, ²sched_dep_time, ³dep_delay, ⁴arr_time, …
</CODE></PRE>

---

# Read as Arrow dataset

``` r
ds <- arrow::open_dataset("flights.parquet")
ds
```

```
FileSystemDataset with 1 Parquet file
19 columns
year: int32
month: int32
day: int32
dep_time: int32
sched_dep_time: int32
dep_delay: double
arr_time: int32
sched_arr_time: int32
arr_delay: double
carrier: string
flight: int32
tailnum: string
origin: string
dest: string
air_time: double
distance: double
hour: double
minute: double
time_hour: timestamp[us]
```
]

``` r
ds |> 
  count(year, month, day) |> 
  collect()
```

<PRE class="fansi fansi-output"><CODE># A tibble: 365 × 4
 year month day n
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;
1 2013 1 1 842
2 2013 1 2 943
3 2013 1 3 914
4 2013 1 4 915
5 2013 1 5 720
6 2013 1 6 832
# ℹ 359 more rows
</CODE></PRE>
]

---

# Register as duckdb lazy table

``` r
con_memory <- dbConnect(
 duckdb::duckdb(), dbdir = ":memory:")

tbl <- duckdb::tbl_file(
 con_memory, "flights.parquet")
tbl |> 
 explain()
```

```
<SQL>
FROM 'flights.parquet'

<PLAN>
physical_plan
┌───────────────────────────┐
│ PARQUET_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ year │
│ month │
│ day │
│ dep_time │
│ sched_dep_time │
│ dep_delay │
│ arr_time │
│ sched_arr_time │
│ arr_delay │
│ carrier │
│ flight │
│ tailnum │
│ origin │
│ dest │
│ air_time │
│ distance │
│ hour │
│ minute │
│ time_hour │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 336776 │
└───────────────────────────┘ 
```
]

``` r
tbl |>
  count(year, month, day)
```

---

# Register as duckplyr lazy data frame

``` r
duckplyr_df <- duckplyr::duckplyr_df_from_parquet("flights.parquet")
class(duckplyr_df)
```

```
[1] "duckplyr_df" "tbl_df"      "tbl"         "data.frame" 
```
]

``` r
duckplyr_df |> 
  explain()
```

```
┌───────────────────────────┐
│       READ_PARQUET        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            year           │
│           month           │
│            day            │
│          dep_time         │
│       sched_dep_time      │
│         dep_delay         │
│          arr_time         │
│       sched_arr_time      │
│         arr_delay         │
│          carrier          │
│           flight          │
│          tailnum          │
│           origin          │
│            dest           │
│          air_time         │
│          distance         │
│            hour           │
│           minute          │
│         time_hour         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 336776        │
└───────────────────────────┘                             
```
]

``` r
duckplyr_df
```

---

# The future: Register as duckplyr lazy data frame

``` r
duckplyr_df |>
  count(year, month, day) |>
  filter(month == 1)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 31 × 4
 year month day n
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;
1 2013 1 1 842
2 2013 1 2 943
3 2013 1 3 914
4 2013 1 4 915
5 2013 1 5 720
6 2013 1 6 832
# ℹ 25 more rows
</CODE></PRE>
]

``` r
duckplyr_df |>
  count(year, month, day) |>
  filter(month == 1) |> 
  explain()
```

```
┌───────────────────────────┐
│          ORDER_BY         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          ORDERS:          │
│dataframe_105553178666960_7│
│    62464429."year" ASC    │
│dataframe_105553178666960_7│
│    62464429."month" ASC   │
│dataframe_105553178666960_7│
│     62464429."day" ASC    │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            year           │
│           month           │
│            day            │
│             n             │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             #0            │
│             #1            │
│             #2            │
│        count_star()       │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            year           │
│           month           │
│            day            │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│   r_base::==(month, 1.0)  │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 67355         │
└─────────────┬─────────────┘                             
┌─────────────┴─────────────┐
│     R_DATAFRAME_SCAN      │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         data.frame        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            year           │
│           month           │
│            day            │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 336776        │
└───────────────────────────┘                             
```
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 450px

# Prepare partitioned dump

``` r
arrow::write_dataset(
  nycflights13::flights,
  "flights-part/",
  partitioning = c("year", "month")
)

fs::dir_tree("flights-part")
```
]

.pull-right[
<PRE class="fansi fansi-output"><CODE>flights-part
└── year=2013
 ├── month=1
 │ └── part-0.parquet
 ├── month=10
 │ └── part-0.parquet
 ├── month=11
 │ └── part-0.parquet
 ├── month=12
 │ └── part-0.parquet
 ├── month=2
 │ └── part-0.parquet
 ├── month=3
 │ └── part-0.parquet
 ├── month=4
 │ └── part-0.parquet
 ├── month=5
 │ └── part-0.parquet
 ├── month=6
 │ └── part-0.parquet
 ├── month=7
 │ └── part-0.parquet
 ├── month=8
 │ └── part-0.parquet
 └── month=9
 └── part-0.parquet
</CODE></PRE>
]

---

# Read partitioned data

``` r
tbl_part <- duckdb::tbl_query(
 con_memory,
 "read_parquet('flights-part/*/*/*.parquet', hive_partitioning = true)"
)
tbl_part
```

<PRE class="fansi fansi-output"><CODE># Source: SQL [?? x 19]
# Database: DuckDB v1.0.0
 day dep_time sched_dep…¹ dep_d…² arr_t…³ sched…⁴ arr_d…⁵ carrier flight tailnum origin
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;chr&gt; &lt;int&gt; &lt;chr&gt; &lt;chr&gt; 
1 1 517 515 2 830 819 11 UA 1545 N14228 EWR 
2 1 533 529 4 850 830 20 UA 1714 N24211 LGA 
3 1 542 540 2 923 850 33 AA 1141 N619AA JFK 
4 1 544 545 -1 1004 1022 -18 B6 725 N804JB JFK 
5 1 554 600 -6 812 837 -25 DL 461 N668DN LGA 
6 1 554 558 -4 740 728 12 UA 1696 N39463 EWR 
# ℹ more rows
# ℹ abbreviated names: ¹sched_dep_time, ²dep_delay, ³arr_time, ⁴sched_arr_time, …
</CODE></PRE>

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Prepare CSV dump

Imagine someone gave you a CSV file.

``` r
readr::write_csv(nycflights13::flights, "flights.csv")

fs::file_size("flights.csv")
```

```
29.6M
```

``` r
lobstr::obj_size(nycflights13::flights)
```

```
40.65 MB
```

---

# Read CSV data with duckdb

``` r
tbl_csv <- duckdb::tbl_file(
 con_memory, "flights.csv")

tbl_csv |> 
  explain()
```

```
<SQL>
FROM 'flights.csv'

<PLAN>
physical_plan
┌───────────────────────────┐
│ READ_CSV_AUTO │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ year │
│ month │
│ day │
│ dep_time │
│ sched_dep_time │
│ dep_delay │
│ arr_time │
│ sched_arr_time │
│ arr_delay │
│ carrier │
│ flight │
│ tailnum │
│ origin │
│ dest │
│ air_time │
│ distance │
│ hour │
│ minute │
│ time_hour │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 326882 │
└───────────────────────────┘ 
```
]

``` r
tbl_csv |>
  count(year, month, day)
```

<PRE class="fansi fansi-output"><CODE># Source: SQL [?? x 4]
# Database: DuckDB v1.0.0
 year month day n
 &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
1 2013 7 11 1006
2 2013 7 15 999
3 2013 7 16 996
4 2013 7 22 1000
5 2013 7 26 999
6 2013 7 31 1001
# ℹ more rows
</CODE></PRE>
]
<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_21.R"><title>Script: db_21.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_21.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_21.R</text></g></svg></div>

---

# Read CSV data with duckplyr

``` r
duckplyr_df_csv <- duckplyr::duckplyr_df_from_csv("flights.csv")
class(duckplyr_df_csv)
```

```
[1] "duckplyr_df" "tbl_df"      "tbl"        
[4] "data.frame" 
```

``` r
duckplyr_df_csv |> 
  explain()
```

```
┌───────────────────────────┐
│       READ_CSV_AUTO       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            year           │
│           month           │
│            day            │
│          dep_time         │
│       sched_dep_time      │
│         dep_delay         │
│          arr_time         │
│       sched_arr_time      │
│         arr_delay         │
│          carrier          │
│           flight          │
│          tailnum          │
│           origin          │
│            dest           │
│          air_time         │
│          distance         │
│            hour           │
│           minute          │
│         time_hour         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         EC: 326882        │
└───────────────────────────┘                             
```
]

``` r
duckplyr_df_csv |>
  count(year, month, day)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 365 × 4
 year month day n
 &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;int&gt;
1 2013 1 1 842
2 2013 1 2 943
3 2013 1 3 914
4 2013 1 4 915
5 2013 1 5 720
6 2013 1 6 832
# ℹ 359 more rows
</CODE></PRE>
]

---

# Create derived Parquet data with duckplyr

``` r
duckplyr_df_csv |>
  count(year, month, day) |>
  duckplyr::df_to_parquet(
    "flights-count.parquet")

fs::file_size("flights-count.parquet")
```

```
2.13K
```
]

``` r
duckplyr::duckplyr_df_from_parquet(
  "flights-count.parquet")
```

<PRE class="fansi fansi-output"><CODE># A tibble: 365 × 4
 year month day n
 &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;int&gt;
1 2013 1 1 842
2 2013 1 2 943
3 2013 1 3 914
4 2013 1 4 915
5 2013 1 5 720
6 2013 1 6 832
# ℹ 359 more rows
</CODE></PRE>
]

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: contain
background-position: 100% 100%

1. From the Parquet file, compute two lazy dbplyr tables
   showing the mean and median delay:
   - for each day of the week
   - for each month
2. Compute the same data as duckplyr lazy data frames.
3. Store this data as a Parquet file.
4. Read the Parquet file and plot the data.
]

<div class="exercise-timer"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="84" height="20" role="img" aria-label="Timer: 10:00"><title>Timer: 10:00</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="84" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="41" height="20" fill="#555"/><rect x="41" width="43" height="20" fill="#007ec6"/><rect width="84" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="215" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="310">Timer</text><text x="215" y="140" transform="scale(.1)" fill="#fff" textLength="310">Timer</text><text aria-hidden="true" x="615" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">10:00</text><text x="615" y="140" transform="scale(.1)" fill="#fff" textLength="330">10:00</text></g></svg></div>

---

# DuckDB showcase

.middle[
<img src="data:image/png;base64,#images/hub.svg" width="130%" height="130%" style="display: block; margin: auto;" />
]

---

# Application: DBI + dbplyr and pivoting

``` r
con_memory <- dbConnect(
 duckdb::duckdb(), dbdir = ":memory:")
tbl <- duckdb::tbl_file(
 con_memory, "flights.parquet")

daily_flights_by_dest <-
 tbl |>
 count(year, month, day, dest)
```
]

``` r
daily_flights_by_dest
```

<PRE class="fansi fansi-output"><CODE># Source: SQL [?? x 5]
# Database: DuckDB v1.0.0
 year month day dest n
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;chr&gt; &lt;dbl&gt;
1 2013 6 30 PHX 13
2 2013 6 30 LGB 2
3 2013 6 30 PWM 7
4 2013 6 30 AUS 7
5 2013 6 30 SMF 1
6 2013 6 30 MCI 3
# ℹ more rows
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

# Application: DBI + dbplyr and pivoting

``` r
daily_flights_by_dest_sql <-
 daily_flights_by_dest |>
 dbplyr::sql_render()
daily_flights_by_dest_sql
```

```
<SQL> SELECT "year", "month", "day", dest, COUNT(*) AS n
FROM (FROM 'flights.parquet') q01
GROUP BY "year", "month", "day", dest
```
]

```
<SQL> PIVOT (SELECT "year", "month", "day", dest, COUNT(*) AS n
FROM (FROM 'flights.parquet') q01
GROUP BY "year", "month", "day", dest) ON dest USING SUM(n)
```
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

# Application: DBI + dbplyr and pivoting

``` r
as_tibble(dbGetQuery(con_memory, pivot_sql))
```

<PRE class="fansi fansi-output"><CODE># A tibble: 365 × 108
 year month day ABQ ACK ALB ANC ATL AUS AVL BDL BGR BHM BNA
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt; &lt;dbl&gt;
1 2013 1 1 NA NA 3 NA 40 6 1 2 NA NA 10
2 2013 1 2 NA NA 3 NA 51 7 1 2 NA 1 11
3 2013 1 3 NA NA 2 NA 49 6 NA 1 NA 1 11
4 2013 1 4 NA NA 2 NA 48 6 NA 1 NA 1 11
5 2013 1 5 NA NA 2 NA 35 4 NA 2 NA NA 8
6 2013 1 6 NA NA 2 NA 41 5 NA 1 NA 1 13
# ℹ 359 more rows
# ℹ 94 more variables: BOS &lt;dbl&gt;, BQN &lt;dbl&gt;, BTV &lt;dbl&gt;, BUF &lt;dbl&gt;, BUR &lt;dbl&gt;, …
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

# Application: DBI + dbplyr and pivoting

``` r
system.time(
  as_tibble(dbGetQuery(
    con_memory, pivot_sql))
)
```

```
   user  system elapsed 
  0.083   0.003   0.041 
```
]

``` r
system.time(
  nycflights13::flights |>
    count(year, month, day, dest) |>
    tidyr::pivot_wider(
      names_from = dest, values_from = n)
)
```

```
   user  system elapsed 
  0.149   0.001   0.150 
```
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

# Application: DBI + dbplyr and pivoting

``` r
write_pivot_sql <- paste0(
 "COPY (", pivot_sql, ") TO 'pivot.parquet' (FORMAT PARQUET)"
)
dbExecute(con_memory, write_pivot_sql)
```

```
[1] 365
```

``` r
q_unpivot_dyn <-
 "(SELECT * FROM (UNPIVOT 'pivot.parquet'
 ON COLUMNS(* EXCLUDE (year, month, day))
 INTO NAME dest VALUE n))"
SQL(q_unpivot_dyn)
```

```
<SQL> (SELECT * FROM (UNPIVOT 'pivot.parquet'
 ON COLUMNS(* EXCLUDE (year, month, day))
 INTO NAME dest VALUE n))
```

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

# Application: DBI + dbplyr and pivoting

``` r
tbl(con_memory, from = q_unpivot_dyn)
```

<PRE class="fansi fansi-output"><CODE># Source: SQL [?? x 5]
# Database: DuckDB v1.0.0
 year month day dest n
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;chr&gt; &lt;dbl&gt;
1 2013 1 1 ALB 3
2 2013 1 1 ATL 40
3 2013 1 1 AUS 6
4 2013 1 1 AVL 1
5 2013 1 1 BDL 2
6 2013 1 1 BNA 10
# ℹ more rows
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

background-image: url("data:image/png;base64,#images/21-frame.webp")
background-size: contain
background-position: 100% 100%

Discuss in groups of 4-6 people:

- How often is the data you work with updated? Realtime, daily, monthly, never?
- Does the size of the data you are working with justify the overhead
  of working with local database dumps?
- Could the proposed workflow be implemented in your current projects?
  What constraints would you face?
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_22.R"><title>Script: db_22.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_22.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_22.R</text></g></svg></div>

---

---

background-image: url("data:image/png;base64,#images/22.webp")
background-size: 40%
background-position: 100% 100%

# Full circle

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

```r
library(DBI)
library(dplyr)
requireNamespace("arrow")
```
]

---

background-image: url("data:image/png;base64,#images/22-frame.webp")
background-size: 40%
background-position: 100% 100%

# Full circle

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

```r
library(DBI)
library(dplyr)
requireNamespace("arrow")
```
]

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 450px

# Prepare database

``` r
con_rw <- dbConnect(
 duckdb::duckdb(), 
 dbdir = "flights.duckdb"
)
copy_to(
 con_rw,
 nycflights13::flights,
 name = "flights",
 temporary = FALSE
)
dbDisconnect(con_rw)
```
]

``` r
con <- dbConnect(
 duckdb::duckdb(),
 dbdir = "flights.duckdb",
 read_only = TRUE
)
flights_duckdb <- tbl(con, "flights")
flights_duckdb
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;flights&gt; [?? x 19]
# Database: DuckDB v1.0.0
 year month day dep_time sched_d…¹ dep_d…²
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt;
1 2013 1 1 517 515 2
2 2013 1 1 533 529 4
3 2013 1 1 542 540 2
4 2013 1 1 544 545 -1
5 2013 1 1 554 600 -6
6 2013 1 1 554 558 -4
# ℹ more rows
# ℹ abbreviated names: ¹sched_dep_time, …
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

# Method 1: via local data frame

``` r
flights_duckdb |>
  filter(month == 1) |>
  collect() |>
  duckplyr::df_to_parquet(
    "flights-jan.parquet")
```
]

``` r
flights_duckdb |>
  collect() |>
  duckplyr::df_to_parquet("flights.parquet")
```
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

# Method 2: via DBI

``` r
sql_jan <- flights_duckdb |>
 filter(month == 1) |>
 dbplyr::sql_render()
sql_jan
```

```
<SQL> SELECT flights.*
FROM flights
WHERE ("month" = 1.0)
```

``` r
fs::dir_create("flights-arrow")
```
]

``` r
res <- dbSendQuery(con, sql_jan)
i <- 0
repeat {
 df <- dbFetch(res, n = 10000)
 if (nrow(df) == 0) break
 path <- fs::path(
 "flights-arrow",
 sprintf("part-%05d.parquet", i))
 duckplyr::df_to_parquet(df, path)
 i <- i + 1
}
dbClearResult(res)
```
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

# Method 3: via parquetize

``` r
parquetize::dbi_to_parquet(
  con,
  sql_jan,
  "flights-parquetized",
  max_rows = 10000
)

fs::dir_tree("flights-parquetized/")
```

<PRE class="fansi fansi-output"><CODE>flights-parquetized/
├── part-1-10000.parquet
├── part-10001-20000.parquet
└── part-20001-27004.parquet
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Method 4: via DBI and arrow

``` r
con_adbi <- dbConnect(
 adbi::adbi(duckdb::duckdb_adbc()),
 path = "flights.duckdb"
)

sql <- "SELECT * FROM flights"
```
]

``` r
system.time({
 res <- dbSendQueryArrow(con_adbi, sql)
 stream <- dbFetchArrow(res)
 arrow::write_dataset(
 arrow::as_record_batch_reader(stream),
 "flights-adbi/",
 max_rows_per_file = 10000
 )
 dbClearResult(res)
})
```

```
   user  system elapsed 
  0.372   0.159   0.175 
```
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

# Adding partitions to a dataset

``` r
write_month <- function(month) {
 sql <- flights_duckdb |>
 filter(month == !!month) |>
 dbplyr::sql_render()

dir <- fs::path(
 "flights-part-manual",
 sprintf("month=%d", month))
 fs::dir_create(dir)

df <- dbGetQuery(con, sql)
 duckplyr::df_to_parquet(
 df,
 fs::path(dir, "part-0.parquet"))
}
```
]

``` r
write_month(1)
write_month(2)
write_month(3)

fs::dir_tree("flights-part-manual")
```

<PRE class="fansi fansi-output"><CODE>flights-part-manual
├── month=1
│ └── part-0.parquet
├── month=2
│ └── part-0.parquet
└── month=3
 └── part-0.parquet
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

background-image: url("data:image/png;base64,#images/22-frame.webp")
background-size: 40%
background-position: 100% 100%

# Full circle: Exercises

.pull-left[
 Write code to create a partitioned dataset with the `flights` table,
partitioned by `origin`.
  - Hint: The dataset only contains flights departing from New York City airports.

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_23.R"><title>Script: db_23.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_23.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_23.R</text></g></svg></div>

---

# https://posit-conf-2024.github.io/databases/part-3.html